package com.lizi.core.util;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.imageio.stream.FileImageInputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel工具类.
 * 
 * @author MarkHsiu
 * 
 */
public class ExcelTools {
	
	private static Logger logger = Logger.getLogger(ExcelTools.class);
	
	private static final ClientAnchor.AnchorType AnchorType = ClientAnchor.AnchorType.MOVE_DONT_RESIZE;

	public static String YUAN_FORMAT_PATTEN = "#0";//#0.00
	 //默认保留的小数位数
    public static int DEFAULT_DIV_SCALE = 2;
    
	/**
	 * 保存excel到服务器上.
	 * 
	 * @param title
	 *            excel标题
	 * @param filepath
	 *            文件路径
	 * @param headers
	 *            表格属性列名数组 例如 ：{ "姓名", "学号", "学部"}
	 * @param keys
	 *            数据键数组， 例如 ：{ "XM","XHGH","XB"}
	 * @param dataset
	 *            需要显示的数据集合
	 * 
	 */
	public void saveExcel(String title,String basepath,String filename,
			String[] headers, String[] keys, List<Map<String, Object>> dataset) {

		// 判断已知路径是否存在
		File path = new File(basepath);
		if (!path.exists()) {
			path.mkdirs();
		} 
		
		 // 判断已知路径下的文件名称是否存在
        File pathFile = new File(basepath+filename);
        if (!pathFile.exists()) {
            try {
				pathFile.createNewFile();
			} catch (IOException e) {
				e.printStackTrace();
			}
        }
		FileOutputStream fileout;
		try {
			fileout = new FileOutputStream(pathFile);
			exportExcel(title==null?"导出EXCEL文档":title, headers, keys, dataset, fileout, "yyyy-MM-dd");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 下载excel到本地.
	 * 
	 * @param title
	 *            表格标题名，例如 ：本科生新生数据
	 * @param headers
	 *            表格属性列名数组 例如 ：{ "姓名", "学号", "学部"}
	 * @param keys
	 *            数据键数组， 例如 ：{ "XM","XHGH","XB"}
	 * @param dataset
	 *            需要显示的数据集合
	 * @param response
	 * 
	 */
	public void exportExcel(String title, String[] headers, String[] keys,
			List<Map<String, Object>> dataset, HttpServletResponse response) {
		try {
			title = new String(title.getBytes("GBK"), "iso-8859-1");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		response.setContentType("octets/stream");
		response.addHeader("Content-Disposition", "attachment;filename="
				+ title + ".xls");
		OutputStream out;
		try {
			out = response.getOutputStream();
			exportExcel("导出EXCEL文档", headers, keys, dataset, out, "yyyy-MM-dd");
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
 
	public void exportExcel(String[] headers, String[] keys,
			List<Map<String, Object>> dataset, OutputStream out) {
		exportExcel("导出EXCEL文档", headers, keys, dataset, out, "yyyy-MM-dd");
	}

	/**
	 * 这是一个通用的方法，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上.
	 * 
	 * @param title
	 *            表格标题名
	 * @param headers
	 *            表格属性列名数组
	 * @param keys
	 *            数据键数组
	 * @param dataset
	 *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
	 *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
	 * @param out
	 *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern
	 *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
	 */
	public void exportExcel(String title, String[] headers, String[] keys,
			List<Map<String, Object>> dataset, OutputStream out, String pattern) {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(15);
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.VIOLET.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);
		// 生成并设置另一个样式
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 生成另一个字体
		HSSFFont font2 = workbook.createFont();
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style2.setFont(font2);

		// 声明一个画图的顶级管理器
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		// 定义注释的大小和位置,详见文档
		HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
				0, 0, 0, (short) 4, 2, (short) 6, 5));
		// 设置注释内容
		comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
		// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
		comment.setAuthor("leno");
		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}

		HSSFFont font3 = workbook.createFont();
		font3.setColor(HSSFColor.BLUE.index);
		try {
			// 遍历集合数据，产生数据行
			for (int index = 1; index <= dataset.size(); index++) {
				row = sheet.createRow(index);

				Map<String, Object> map = dataset.get(index - 1);
				for (int i = 0; i < keys.length; i++) {
					HSSFCell cell = row.createCell(i);
					cell.setCellStyle(style2);

					Object value = map.get(keys[i]);
					// 判断值的类型后进行强制类型转换
					String textValue = null;

					if (value instanceof Date) {
						Date date = (Date) value;
						SimpleDateFormat sdf = new SimpleDateFormat(pattern);
						textValue = sdf.format(date);
					} else if (value instanceof byte[]) {
						// 有图片时，设置行高为60px;
						row.setHeightInPoints(60);
						// 设置图片所在列宽度为80px,注意这里单位的一个换算
						sheet.setColumnWidth(i, (short) (35.7 * 80));
						// sheet.autoSizeColumn(i);
						byte[] bsValue = (byte[]) value;
						// Parameters:
						// dx1 - the x coordinate within the first cell.
						// dy1 - the y coordinate within the first cell.
						// dx2 - the x coordinate within the second cell.
						// dy2 - the y coordinate within the second cell.
						// col1 - the column (0 based); of the first cell.
						// row1 - the row (0 based); of the first cell.
						// col2 - the column (0 based); of the second cell.
						// row2 - the row (0 based); of the second cell.
						HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
								1023, 255, (short) i, index, (short) i, index);
						anchor.setAnchorType(AnchorType);
						patriarch.createPicture(anchor, workbook.addPicture(
								bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
					} else {						
						// 其它数据类型都当作字符串简单处理
						textValue = value==null?null:value.toString();
					}
					// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
					if (textValue != null) {
						if(textValue.length() >= SpreadsheetVersion.EXCEL2007.getMaxTextLength()){
							textValue = "111maxTextLength";
							System.out.println(keys[i]);
						}
						Pattern p = Pattern.compile("^//d+(//.//d+)?{1}quot;");
						Matcher matcher = p.matcher(textValue);
						if (matcher.matches()) {
							// 是数字当作double处理
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							HSSFRichTextString richString = new HSSFRichTextString(
									textValue);	
							richString.applyFont(font3);
							cell.setCellValue(richString);
						}
					}
				}
			}
		} catch (SecurityException e) {
			e.printStackTrace();
		} finally {
			// 清理资源
		}

		try {
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}

	}
	
 
	 
	/**
	 * Excel文件读取.
	 * 
	 * @param columns
	 *            [] 字符串数组，存放的是数据库中对应的列名
	 * @param filepath
	 *            文件路径
	 * @param index
	 *            从第几行开始读取 ，默认为第一行，即index=1
	 * @return 数据结果集
	 * @author MarkHsiu
	 */
	public List<Map<String, Object>> impotExcel(String columns[],
			String filepath, int index) {

		filepath = filepath.replace("/", "//");

		if (index <= 0) {
			index = 1;
		}

		boolean flag = "xls".equals(filepath.substring(filepath.lastIndexOf(".") + 1)
				.toLowerCase());// 是否是03格式

		List<Map<String, Object>> list = null;
		if (filepath != null && !"".equals(filepath)) {
			if (flag) {
				list = getHSSFSheet(columns, filepath, index);
			} else {
				list = getXSSFSheet(columns, filepath, index);
			}
		}
		return list;
	}

	/**
	 * 读取excel 2003 数据.
	 * 
	 * @param columns
	 *            [] 字符串数组，存放的是数据库中对应的列名
	 * @param filepath
	 *            文件路径
	 * @param list
	 *            数据结果集
	 * @param index
	 *            从第几行开始读取 ，默认为第一行，即index=1
	 * @return
	 */
	private List<Map<String, Object>> getHSSFSheet(String columns[],
			String filepath, int index) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		HSSFWorkbook hwk = null;
		try {
			hwk = new HSSFWorkbook(new FileInputStream(filepath));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		HSSFSheet sh = hwk.getSheetAt(0);
		int rows = sh.getLastRowNum() + 1 - sh.getFirstRowNum();// 行数
		if (rows <= 1) {
			logger.info("数据必须从第二行开始并且不能为空！");
			return null;
		}

		HSSFRow rowFirst = sh.getRow(0);
		int cols = rowFirst.getLastCellNum() - rowFirst.getFirstCellNum();// 列数
		if (cols != columns.length) {
			logger.info("excel列数不相符，请检查!");
			return null;
		}
		cols = columns.length;

		for (int i = index; i < rows; i++) {
			HSSFRow row = sh.getRow(i);
			// 判断行是否为空
			if (!isBlankHSSFRow(row)) {
				// 该行的总列数
				Map<String, Object> map = new HashMap<String, Object>();
				for (int j = 0; j < cols; j++) {
					HSSFCell cell = row.getCell(j);
					// * 读取excel 2003数据
					map.put(columns[j], getCellValue(cell));
				}
				list.add(map);
			}
		}
		return list;
	}

	/**
	 * 读取excel 2007 数据.
	 * 
	 * @param columns
	 *            [] 字符串数组，存放的是数据库中对应的列名
	 * @param filepath
	 *            文件路径
	 * @param list
	 *            数据结果集
	 * @param index
	 *            从第几行开始读取 ，默认为第一行，即index=1
	 * @return
	 */
	private List<Map<String, Object>> getXSSFSheet(String columns[],
			String filepath, int index) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		XSSFWorkbook wb = null;
		try {
			wb = new XSSFWorkbook(new FileInputStream(filepath));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		XSSFSheet sh = wb.getSheetAt(0);
		int rows = sh.getLastRowNum() + 1 - sh.getFirstRowNum();// 行数
		if (rows == 0) {
			return null;
		}

		XSSFRow rowFirst = sh.getRow(0);
		int cols = rowFirst.getLastCellNum() - rowFirst.getFirstCellNum();// 列数
		if (cols < columns.length) {
			return null;
		}
		cols = columns.length;

		for (int i = index; i < rows; i++) {
			XSSFRow row = sh.getRow(i);
			// 判断行是否为空
			if (!isBlankXSSFRow(row)) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int j = 0; j < cols; j++) {
					XSSFCell cell = row.getCell(j);
					// * 读取excel 2007 数据
					map.put(columns[j], getXCellValue(cell));
				}
				list.add(map);

			}
		}
		return list;
	}

	/**
	 * 判断从2003Excel文件中解析出来数据的格式.
	 * 
	 * @return
	 * @author
	 */
	private String getCellValue(HSSFCell cell) {
		if (cell == null) {
			return null;
		}

		String value = null;
		// 简单的查检列类型
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:// 字符串
			value = cell.getRichStringCellValue().getString().trim();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:// 数字
			if (HSSFDateUtil.isCellDateFormatted(cell)) { // 判断是日期类型
				SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");
				Date dt = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());// 获取成DATE类型
				value = dateformat.format(dt);
			} else {
				double dd =  cell.getNumericCellValue();
				value = ExcelTools.getRealNumberStr(dd); 
			}
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			value = "";
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			value = cell.getCellFormula();
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:// boolean型值
			value = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_ERROR:
			value = String.valueOf(cell.getErrorCellValue());
			break;
		default:
			break;
		}
		return value == null ? null : value.trim();
	}

	/**
	 * 判断从2007Excel文件中解析出来数据的格式.
	 * 
	 * @return
	 * @author
	 */
	private String getXCellValue(XSSFCell cell) {
		if (cell == null) {
			return null;
		}

		String value = null;
		// 简单的查检列类型
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:// 字符串
			value = cell.getRichStringCellValue().getString().trim();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:// 数字
			if (HSSFDateUtil.isCellDateFormatted(cell)) { // 判断是日期类型
				SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");
				Date dt = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());// 获取成DATE类型
				value = dateformat.format(dt);
			} else {
				double dd =  cell.getNumericCellValue();
				value = ExcelTools.getRealNumberStr(dd); 
			}
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			value = "";
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			value = cell.getCellFormula();
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:// boolean型值
			value = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_ERROR:
			value = String.valueOf(cell.getErrorCellValue());
			break;
		default:
			break;
		}
		return value == null ? null : value.trim();
	}
	
	 /**
     * 将数字转换为字符串
     * 保留2位小数，并格式化为 xx.00
     *
     * @param num
     * @return
     */
    public static String getRealNumberStr(double num){
        BigDecimal b = new BigDecimal(Double.toString(num));
        BigDecimal one = new BigDecimal("1");
        return new DecimalFormat(YUAN_FORMAT_PATTEN).format(b.divide(one, DEFAULT_DIV_SCALE, BigDecimal.ROUND_HALF_UP));
    }

	/**
	 * 2003 判断为空或只有空格的row忽略.
	 * 
	 * @param row
	 * @return
	 */
	public boolean isBlankXSSFRow(XSSFRow row) {
		if (row == null) {
			return true;
		}
		boolean result = true;
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			XSSFCell cell = row.getCell(i, XSSFRow.RETURN_BLANK_AS_NULL);
			String value = "";
			if (cell != null) {
				switch (cell.getCellType()) {
				case Cell.CELL_TYPE_STRING:
					value = cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_NUMERIC:
					value = String.valueOf((int) cell.getNumericCellValue());
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					value = String.valueOf(cell.getBooleanCellValue());
					break;
				case Cell.CELL_TYPE_FORMULA:
					value = cell.getCellFormula();
					break;
				// case Cell.CELL_TYPE_BLANK:
				// break;
				default:
					break;
				}

				if (!"".equals(value.trim())) {
					result = false;
					break;
				}
			}
		}

		return result;
	}

	/**
	 * 2007 判断为空或只有空格的row忽略.
	 * 
	 * @param row
	 * @return
	 */
	public boolean isBlankHSSFRow(HSSFRow row) {
		if (row == null) {
			return true;
		}
		boolean result = true;
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			HSSFCell cell = row.getCell(i, HSSFRow.RETURN_BLANK_AS_NULL);
			String value = "";
			if (cell != null) {
				switch (cell.getCellType()) {
				case Cell.CELL_TYPE_STRING:
					value = cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_NUMERIC:
					value = String.valueOf((int) cell.getNumericCellValue());
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					value = String.valueOf(cell.getBooleanCellValue());
					break;
				case Cell.CELL_TYPE_FORMULA:
					value = cell.getCellFormula();
					break;
				// case Cell.CELL_TYPE_BLANK:
				// break;
				default:
					break;
				}

				if (!"".equals(value.trim())) {
					result = false;
					break;
				}
			}
		}

		return result;
	}

	public static void main(String[] args) throws FileNotFoundException,
			IOException {
		long start = System.currentTimeMillis();

		ExcelTools u = new ExcelTools();
		String filepath1 = "E:\\本科生.xls";
		String filepath2 = "E:\\研究生新生基本信息.xlsx";
		String filepath3 = "D:\\WINDOW\\HUAJIANHSIU\\Desktop\\样表.xlsx";
		String leibiao[] = { "XHGH", "CJBZ", "JKZK", "TJBZ", "AA","X121HGH", "CJ213BZ", "J123KZK", "123", "A123A","A"};
		List<Map<String, Object>> list = u.impotExcel(leibiao, filepath3, 1);
		
		for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i));
		}
		System.out.println(list.size());
		System.out.println(System.currentTimeMillis() - start + "毫秒");
	}
	/** 图片到byte数组. */
	  public byte[] image2byte(String path){
	    byte[] data = null;
	    FileImageInputStream input = null;
	    try {
	      input = new FileImageInputStream(new File(path));
	      ByteArrayOutputStream output = new ByteArrayOutputStream();
	      byte[] buf = new byte[1024];
	      int numBytesRead = 0;
	      while ((numBytesRead = input.read(buf)) != -1) {
	      output.write(buf, 0, numBytesRead);
	      }
	      data = output.toByteArray();
	      output.close();
	      input.close();
	    }
	    catch (FileNotFoundException ex1) {
	      ex1.printStackTrace();
	    }
	    catch (IOException ex1) {
	      ex1.printStackTrace();
	    }
	    return data;
	  }
}
